* Table 6
* Collaboration 
local table "Table6"
use "./data/publications.dta", clear
createvarspub
keep if year>1999
qui putexcel set "./tables/tables.xlsx", sheet("`table'") modify
qui putexcel A1=("Institution")
qui putexcel B1=("N")
qui putexcel C1=("% Single")
qui putexcel D1=("% In-house")
qui putexcel E1=("% P Aut Nat aff")
qui putexcel F1=("% P Aut For aff")
qui putexcel G1=("% F Aut For aff")
local pos=2
foreach var of varlist ///
ulisboa unova uporto uminho bp ucoimbra iscte ucp ///
uevora ubi uaveiro ualgarve uacores ///
umadeira utad {
qui putexcel A`pos'=("`var'")
unique article_id if `var'==1
local nart=r(unique)
unique article_id if `var'==1 & naut==1 
local nsing=r(unique)
bys article: egen inaut=total(`var')
unique article_id if inaut>1
local inhouse=r(unique)
unique article_id if t`var'==1 & `var'==0 & portnat==1 & national==1
local colpapa=r(unique)
unique article_id if t`var'==1 & `var'==0 & portnat==1 & national==0
local colpafa=r(unique)
unique article_id if t`var'==1 & `var'==0 & portnat==0 & national==0
local colfafa=r(unique)
qui putexcel B`pos'=(`nart'), nformat(#)
local shnsing=`nsing'/`nart'
qui putexcel C`pos'=(`shnsing'), nformat(#.0%)
local shinhouse=`inhouse'/`nart'
qui putexcel D`pos'=(`shinhouse'), nformat(#.0%)
local shcolpapa=`colpapa'/`nart'
qui putexcel E`pos'=(`shcolpapa'), nformat(#.0%)
local shcolpafa=`colpafa'/`nart'
qui putexcel F`pos'=(`shcolpafa'), nformat(#.0%)
local shcolfafa=`colfafa'/`nart'
qui putexcel G`pos'=(`shcolfafa'), nformat(#.0%)
local ++pos
drop inaut
}
